package com.luna.dao;

import com.luna.beans.Book;
import com.luna.utils.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public Book selectByBookId(int book_id) throws Exception {
        Book book = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection con = null;
        String sql = "select * from book where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, book_id);
            rs = ps.executeQuery();
            if (rs.next()) {
                book = new Book(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getInt("uploader_id")
                );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return book;
    }

    public List<Book> selectAllBook() throws Exception {
        List<Book> list = new ArrayList<Book>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from book";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Book(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getInt("uploader_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<Book> selectAllBookByUploaderId(int uploader_id) throws Exception {
        List<Book> list = new ArrayList<Book>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from book where uploader_id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, uploader_id);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Book(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getInt("uploader_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<Book> selectAllBookByAuthor(String author) throws Exception {
        List<Book> list = new ArrayList<Book>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from book where author=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, author);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Book(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("author"),
                        rs.getInt("uploader_id")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public void insertBook(Book book) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "insert into book(id, name, author, uploader_id) value(NULL, ?, ?, ?)";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, book.getName());
            ps.setString(2, book.getAuthor());
            ps.setInt(3, book.getUploaderId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void dropBookById(int book_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "delete from book where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, book_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void updateBook(int id, Book book) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update book set name=?, author=? where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, book.getName());
            ps.setString(2, book.getAuthor());
            ps.setInt(3, id);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }
}
